package com.asia.special;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;


import com.opensymphony.xwork2.ActionSupport;

public class CreateSQL extends ActionSupport
{
    private static final long serialVersionUID = 1L;

    private String table_name  =null;
    private String table_id    =null;
    private String interface_id=null;
    private String division    =null;
    private String hou_num     =null;
    private String qian_num    =null;
    private String data_source =null;
    private String opp_system  =null;
    private String ods_specification=null;
    private List<String> field_name=null;
    private List<String> qian_id   =null;
    private List<String> hou_id    =null;
    private List<String> field_type=null;
    private List<String> clear_method=null;
    private List<String> ods_field_specification=null;
    private List<String> ods_field_dimen=null;
    private List<String> ods_field_moreinfo=null;
    private String dbType            =null;
    private List<String> keyfield    =null;//�����
    private String keyfield_all      =null;
    private int line_num=0;//�ֶ���Ҫ��ɶ�����insert
    private int file_num=0;//�����ļ��е�*�ָ�Ϊ�������ţ�Ҫ��ɵ��ļ�����
    private int keyfield_num = 0;
    public String CreateSQLfun() throws IOException
    {
        //�������ļ��ж�ȡ
        String path="D:/test1/input.csv";
        File file = new File(path);//��Ŀ¼��ȡ�ļ�
        BufferedReader br = null;
        field_name  = new ArrayList<String>();
        qian_id     = new ArrayList<String>();
        hou_id      = new ArrayList<String>();
        field_type  = new ArrayList<String>();
        clear_method= new ArrayList<String>();
        ods_field_specification = new ArrayList<String>();
        ods_field_dimen         = new ArrayList<String>();
        ods_field_moreinfo      = new ArrayList<String>();
        try
        {
            if (!file.isFile())
            {//������ļ������
                return "false";
            }
            br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));//����ļ���
            String line = null;
            while ((line = br.readLine()) != null)
            {//��ȡһ��
                String[] parts = line.split(",");
                if ("TABLE".equals(parts[0]))
                {
                    if (parts.length > 10)
                    {
                        System.out.println("�����ļ�table�г��?������޶���");
                        return "false";
                    }else
                    {
                        table_name  =parts[1].toUpperCase();
                        table_id    =parts[2].toUpperCase();
                        interface_id=parts[3].toUpperCase();
                        qian_num    =parts[4].toUpperCase();
                        hou_num     =parts[5].toUpperCase();
                        division    =parts[6].toUpperCase();
                        data_source =parts[7].toUpperCase();
                        opp_system  =parts[8].toUpperCase();
                        ods_specification = parts[9].toUpperCase();
                        if (table_name==null) {
                            System.out.println("������Ϊ��");
                        }
                        if (division==null) {
                            System.out.println("�ָ�����Ϊ��");
                        }
                    }
                }else {
                    if ("FIELD".equals(parts[0]))
                    {
                        if (parts.length > 9) {

                        }
                        field_name.add(parts[1].toUpperCase());
                        field_type.add(parts[2].toUpperCase());
                        hou_id.add(parts[3].toUpperCase());
                        qian_id.add(parts[4].toUpperCase());
                        clear_method.add(parts[5].toUpperCase());
                        ods_field_specification.add(parts[6].toUpperCase());
                        ods_field_dimen.add(parts[7].toUpperCase());
                        ods_field_moreinfo.add(parts[8].toUpperCase());
                        line_num++;
                    }else {
                        if ("DBTYPE".equals(parts[0]))
                        {
                            keyfield_num = parts.length-2;
                            keyfield = new ArrayList<String>();
                            dbType=parts[1];
                            for (int i = 0; i < keyfield_num; i++)
                            {
                                keyfield.add(parts[i+2]);
                            }
                            if ("oracle".equals(dbType))
                            {
                                field_type=translate(field_type);
                            }
                        }else {
                            if ("*".equals(parts[0]))
                            {//�����ڶ�������ļ�ʱ��ִ��ѭ����ɶ��sql�ļ�
                                printSQL();
                                file_num++;
                                field_name  = new ArrayList<String>();
                                qian_id     = new ArrayList<String>();
                                hou_id      = new ArrayList<String>();
                                field_type  = new ArrayList<String>();
                                clear_method= new ArrayList<String>();
                                ods_field_specification = new ArrayList<String>();
                                ods_field_dimen         = new ArrayList<String>();
                                ods_field_moreinfo      = new ArrayList<String>();
                            }
                        }
                    }
                }
            }
            //��*��Ϊ���һ�����ò���ʱִ��
            printSQL();
        }
        catch (Exception e) {
            // TODO: handle exception
            e.fillInStackTrace();
            System.out.println("����");
        }
        finally {
            if (br != null)
            {
                br.close();
                if(file.exists())
                {
                    file.delete();
                }
            }
        }
        return SUCCESS;
    }
    public static boolean exportSQL(File file, List<String> dataList)
    {
        boolean isSucess=false;

        FileOutputStream out=null;
        OutputStreamWriter osw=null;
        BufferedWriter bw=null;
        try {
            out = new FileOutputStream(file);
            osw = new OutputStreamWriter(out);
            bw =new BufferedWriter(osw);
            if(dataList!=null && !dataList.isEmpty()){
                for(String data : dataList){
                    bw.append(data).append("\r");
                }
            }
            isSucess=true;
        } catch (Exception e) {
            isSucess=false;
        }finally{
            if(bw!=null){
                try {
                    bw.close();
                    bw=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(osw!=null){
                try {
                    osw.close();
                    osw=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(out!=null){
                try {
                    out.close();
                    out=null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return isSucess;
    }

    public List<String> translate(List<String> fieldtype_old)
    {
        List<String> fieldtype_new=new ArrayList<String>();
        fieldtype_new=fieldtype_old;
        for (int i = 0; i < line_num; i++)
        {
            if (fieldtype_old.get(i).contains("VARCHAR2"))
            {
                fieldtype_new.get(i).replaceAll("VARCHAR2", "VARCHAR");
            }
            if (fieldtype_old.get(i).contains("NUMBER"))
            {
                fieldtype_new.get(i).replaceAll("NUMBER", "BIGINT");
            }
        }
        return fieldtype_new;
    }

    public static void main(String[] args) throws IOException
    {
        CreateSQL createSQL = new CreateSQL();
        createSQL.CreateSQLfun();
    }

    public boolean printSQL()
    {
        int maxfieldlen=0;
        int maxqianlen =0;
        int maxhoulen  =0;
        String toalignField=" ";//����ֶκ�ո񣬱�֤����ĵ�����
        String toalignQian =" ";
        String toalignHou  =" ";

        List<String> dataList=new ArrayList<String>();

        dataList.add("-------"+table_name);
        dataList.add("DELETE FROM USYS_PARA_PRE_SRC_BAK;");
        dataList.add("DELETE FROM USYS_PARA_PRE_DEST_BAK;");
        dataList.add("DELETE FROM USYS_PARA_PRE_MAP_BAK;");
        dataList.add("\r");
        dataList.add("INSERT INTO USYS_PARA_PRE_SRC_BAK  SELECT * FROM USYS_PARA_PRE_SRC;");
        dataList.add("INSERT INTO USYS_PARA_PRE_DEST_BAK SELECT * FROM USYS_PARA_PRE_DEST;");
        dataList.add("INSERT INTO USYS_PARA_PRE_MAP_BAK  SELECT * FROM USYS_PARA_PRE_MAP;");
        dataList.add("\r");
        dataList.add("DELETE FROM USYS_PARA_PRE_SRC  WHERE SRC_FILE_ID ="+table_id+";");
        dataList.add("DELETE FROM USYS_PARA_PRE_DEST WHERE SRC_FILE_ID ="+table_id+";");
        dataList.add("DELETE FROM USYS_PARA_PRE_MAP  WHERE DEST_FILE_ID="+table_id+";");
        dataList.add("\r");
        dataList.add("INSERT INTO USYS_PARA_PRE_SRC  VALUES("+table_id+",'"+interface_id+"',"+hou_num+",'"+division+"');");
        dataList.add("INSERT INTO USYS_PARA_PRE_DEST VALUES("+table_id+","+table_id+",'"+table_name+"',"+qian_num+",'	',null,null);");
        dataList.add("\r");
        for (int i = 0; i < line_num; i++)
        {
            if (qian_id.get(i).length()>maxqianlen)
            {
                maxqianlen=qian_id.get(i).length();
            }
            if (hou_id.get(i).length()>maxhoulen)
            {
                maxhoulen=hou_id.get(i).length();
            }
            if (field_name.get(i).length()>maxfieldlen)
            {
                maxfieldlen=field_name.get(i).length();
            }
        }
        maxfieldlen=maxfieldlen+2;
        for (int i = 0; i < line_num; i++)
        {
            for (int j = 0; j < (maxfieldlen-field_name.get(i).length()); j++)
            {
                toalignField=toalignField+" ";
            }
            for (int j = 0; j < (maxqianlen-qian_id.get(i).length()); j++)
            {
                toalignQian=toalignQian+" ";
            }
            for (int j = 0; j < (maxhoulen-hou_id.get(i).length()); j++)
            {
                toalignHou=toalignHou+" ";
            }
            dataList.add("INSERT INTO USYS_PARA_PRE_MAP VALUES("+table_id+",'"+table_name+"', "+qian_id.get(i)+toalignQian+" ,'"+field_name.get(i)+toalignField+"','settl_rec_define', '".toUpperCase()+hou_id.get(i)+toalignHou+"' ,'' ,'' ,'"+clear_method.get(i)+"' ,null);");
            toalignField=" ";
            toalignQian =" ";
            toalignHou  =" ";
        }
        dataList.add("\r");
        dataList.add("---------("+interface_id+") "+table_name);
        dataList.add("DROP TABLE "+table_name+";");
        dataList.add("CREATE TABLE CQCRM."+table_name+"(");
        for (int i = 0; i < line_num; i++)
        {
            for (int j = 0; j < (maxfieldlen-field_name.get(i).length()); j++)
            {
                toalignField=toalignField+" ";
            }
            dataList.add(" "+field_name.get(i)+toalignField+field_type.get(i)+",");
            toalignField=" ";
        }
        dataList.add(") ");
        for (int i = 0; i < keyfield_num-1; i++)
        {
            keyfield_all=keyfield_all+keyfield.get(i)+",";
        }
        keyfield_all=keyfield_all+keyfield.get(keyfield_num-1);

        dataList.add("PARTITIONING KEY ("+keyfield_all.toUpperCase()+")  USING HASHING");
        dataList.add("IN TBS_ODS INDEX IN TBS_INDEX NOT LOGGED INITIALLY;");
        dataList.add("ALTER TABLE CQCRM."+table_name+" LOCKSIZE TABLE;");

        boolean isSuccess=exportSQL(new File("D:/test1/output"+file_num+".sql"), dataList);
        System.out.println(isSuccess);
        line_num=0;
        return true;
    }
}